package com.smy.baseutil.excel;

import java.io.FileOutputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFHyperlink;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Hyperlink;


public class TableInfoToExcel {


	private static final String URL = "jdbc:mysql://127.0.0.1:3306/test1";
	private static final String NAME = "root";
	private static final String PASS = "abcde";
	private static final String DRIVER = "com.mysql.jdbc.Driver";

	private static final String sql_format = "SELECT  COLUMN_NAME ,  COLUMN_COMMENT ,  COLUMN_TYPE ,  IS_NULLABLE ,  COLUMN_DEFAULT   FROM  INFORMATION_SCHEMA.COLUMNS WHERE table_schema = 'test1' AND table_name = '%s'";

	private static final String FILE_NAME = "F://数据库描述.xls";

	public static void main(String[] args) throws Exception {
		try {
			Class.forName(DRIVER);
		} catch (ClassNotFoundException e1) {
			e1.printStackTrace();
		}

		HSSFWorkbook wb = new HSSFWorkbook();
		HSSFSheet sheet = wb.createSheet("导航页");
		sheet.setDefaultRowHeightInPoints(18);
		sheet.setDefaultColumnWidth(30);
		// 创建连接
		Connection con;
		// 查要生成实体类的表
		String sql = " SELECT table_name , table_comment FROM information_schema.tables WHERE table_schema='test1' order by table_comment ;";
		PreparedStatement pStemt = null;
		con = DriverManager.getConnection(URL, NAME, PASS);
		pStemt = con.prepareStatement(sql);
		ResultSet rs = pStemt.executeQuery();
		int rowIndex = 1;
		while (rs.next()) {
			String tableName = rs.getString(1);
			String comment = rs.getString(2);
			createHSSFSheet(tableName, comment, con, wb, sheet, rowIndex);
			rowIndex++;
		}

		FileOutputStream fileOut = new FileOutputStream(FILE_NAME);
		wb.write(fileOut);
		fileOut.close();
		con.close();
	}

	/**
	 * 根据表名建立sheet 2017年8月31日 boolean_zzh@sina.com 修改备注：
	 * 
	 * @param tableName
	 *            数据库的表名
	 * @param tableComment
	 *            数据库的表备注
	 * @param con
	 * @param wb
	 * @param hrefHSSFSheet
	 * @param hrefRowIndex
	 * @throws SQLException
	 */
	private static void createHSSFSheet(String tableName, String tableComment, Connection con, HSSFWorkbook wb,
			HSSFSheet hrefHSSFSheet, int hrefRowIndex) throws SQLException {
		String sql = String.format(sql_format, tableName);
		PreparedStatement pStemt = null;
		pStemt = con.prepareStatement(sql);
		ResultSet rs = pStemt.executeQuery();
		HSSFSheet sheet = wb.createSheet(tableComment);
		System.out.println("生成[" + tableComment + "]");
		sheet.setDefaultColumnWidth(20);
		setHref(hrefHSSFSheet, hrefRowIndex, tableComment, tableName);
		setTableName(sheet, tableName);
		setRowHead(sheet);
		int rownum = 3;
		while (rs.next()) {
			String columnNmae = rs.getString(1);
			String columnComment = rs.getString(2);
			String columnType = rs.getString(3);
			String isNull = rs.getString(4);
			String defaultValue = rs.getString(5);
			HSSFRow row = sheet.createRow(rownum);
			setCellValue(row, 0, columnNmae);
			setCellValue(row, 1, columnComment);
			setCellValue(row, 2, columnType);
			setCellValue(row, 3, isNull);
			setCellValue(row, 4, defaultValue);
			rownum++;
		}
	}

	private static void setTableName(HSSFSheet sheet, String tableName) {
		HSSFRow row = sheet.createRow(0);
		setCellValue(row, 0, "表名:");
		setCellValue(row, 1, tableName);
	}

	/**
	 * 往第一个sheet页增加跳转单元格 2017年8月31日 boolean_zzh@sina.com 修改备注：
	 * 
	 * @param hrefHSSFSheet
	 * @param hrefRowIndex
	 * @param tableComment
	 */
	private static void setHref(HSSFSheet hrefHSSFSheet, int hrefRowIndex, String tableComment, String tableName) {
		HSSFRow row = (HSSFRow) hrefHSSFSheet.createRow(hrefRowIndex);

		/* 连接跳转 */
		HSSFCell likeCell = row.createCell(0);
		Hyperlink hyperlink = new HSSFHyperlink(Hyperlink.LINK_DOCUMENT);
		// "#"表示本文档 "明细页面"表示sheet页名称 "A10"表示第几列第几行
		String address = "#" + tableComment + "!A1";
		hyperlink.setAddress(address);
		likeCell.setHyperlink(hyperlink);
		// 点击进行跳转
		likeCell.setCellValue(tableComment);
		
		HSSFCell tableCell = row.createCell(1);
		tableCell.setCellValue(tableName);
	}

	/**
	 * 设置每个sheet的默认行 2017年8月31日 boolean_zzh@sina.com 修改备注：
	 * 
	 * @param sheet
	 */
	private static void setRowHead(HSSFSheet sheet) {
		HSSFRow row = sheet.createRow(2);
		setCellValue(row, 0, "列名");
		setCellValue(row, 1, "备注");
		setCellValue(row, 2, "类型");
		setCellValue(row, 3, "是否为空");
		setCellValue(row, 4, "默认值");

	}

	/**
	 * 设置单元格的值 2017年8月31日 boolean_zzh@sina.com 修改备注：
	 * 
	 * @param row
	 * @param index
	 * @param value
	 */
	private static void setCellValue(HSSFRow row, int index, String value) {
		HSSFCell cell = row.createCell(index);
		cell.setCellValue(value);
	}
}
